﻿using System;
using System.Collections.Generic;
using System.Text;
using XDesigner.ORM.DOM;

namespace XDesigner.ORM
{
    /// <summary>
    /// Manage standard relateion .
    /// </summary>
    /// <remarks>
    /// This module use database table name Table_EntryRelation, this table has fields
    /// Field name      Type     Description
    /// --------------------------------------------------------
    /// RecordID        integer   index field to indentfy record 
    /// ParentTableName Text      Parent table name
    /// ParentID        Text      Parent record id
    /// ChildTableName  Text      Child table name
    /// ChildID         Text      Child record id
    /// </remarks>
    public class ORMStandardRelationManager 
    {
        public ORMStandardRelationManager()
        {
        }

        internal ORMEngine _Engine = null;

        public ORMEngine Engine
        {
            get
            { 
                return _Engine; 
            }
        }

        private void CheckState()
        {
            if ( this.Engine == null)
            {
                throw new ArgumentNullException("this.Engine");
            }
        }

        public int SaveChildRelations(
            object parentInstance, 
            Type childType ,
            System.Collections.IEnumerable childInstances)
        {
            // check parameters
            CheckState();
            if (parentInstance == null)
            {
                throw new ArgumentNullException("parentInstance");
            }
            ORMTypeInfo parentType = this.Engine.Project.Types[parentInstance.GetType()];
            if (parentType == null)
            {
                throw new NotSupportedException(parentInstance.GetType().FullName);
            }
            if (parentType.KeyProperty == null)
            {
                throw new NotSupportedException(parentType.InstanceTypeName);
            }
            if (childType == null)
            {
                throw new ArgumentNullException("childType");
            }
            ORMTypeInfo oc = this.Engine.Project.Types[childType];
            if (oc == null)
            {
                throw new NotSupportedException(childType.FullName);
            }
            if (oc.KeyProperty == null)
            {
                throw new NotSupportedException(childType.FullName);
            }
            // get helpers
            ORMTypeMappingHelper parentHelper = this.Engine.MappingHelpers[parentType ] ;
            ORMTypeMappingHelper childHelper = this.Engine.MappingHelpers[ childType ] ;

            ORMContext context = new ORMContext(this.Engine);
            // read parent id value
            object parentKeyValue = parentHelper.GetDBPropertyValue(
                context , 
                parentInstance ,
                parentType.KeyProperty.Name );
            if (parentKeyValue != null && DBNull.Value.Equals(parentKeyValue) == false)
            {
                string parentID = Convert.ToString(parentKeyValue);
                // read child instances id values
                List<string> childIDs = new List<string>();
                string childKeyName = oc.KeyProperty.Name ;
                if (childInstances != null)
                {
                    foreach (object child in childInstances)
                    {
                        object childKeyValue = childHelper.GetDBPropertyValue(
                            context,
                            child, 
                            childKeyName);
                        if (childKeyValue != null 
                            && DBNull.Value.Equals(childKeyValue) == false)
                        {
                            childIDs.Add(Convert.ToString(childKeyValue));
                        }
                    }//foreach
                }
                // save relation table
                return SaveRelation(
                    parentType.TableName,
                    parentID, 
                    oc.TableName,
                    childIDs.ToArray());
            }//if
            return 0;
        }

        public Array ReadChildInstances( object parentInstance, Type childType)
        {
            CheckState();
            if (parentInstance == null)
            {
                throw new ArgumentNullException("parentInstance");
            }
            ORMTypeInfo parentType = this.Engine.Project.Types[parentInstance.GetType()];
            if (parentType == null)
            {
                throw new NotSupportedException(parentInstance.GetType().FullName);
            }
            if (childType == null)
            {
                throw new ArgumentNullException("childType");
            }
            ORMTypeInfo oc = this.Engine.Project.Types[childType];
            if (oc == null)
            {
                throw new NotSupportedException(childType.FullName);
            }
            ORMPropertyInfo keyProperty = oc.KeyProperty;
            if (keyProperty == null)
            {
                throw new NotSupportedException(childType.FullName);
            }

            ORMTypeMappingHelper parentHelper = this.Engine.MappingHelpers[parentType];
            object parentKeyValue = parentHelper.GetDBPropertyValue(
                new ORMContext(this.Engine), 
                parentInstance,
                parentType.KeyProperty.Name);
            if (parentKeyValue != null && DBNull.Value.Equals(parentKeyValue) == false)
            {
                Array ids =  ReadChildIDArray(
                    parentType.TableName,
                    new string[] { Convert.ToString(parentKeyValue) },
                    oc.TableName);

                if (ids != null && ids.Length > 0)
                {
                    StringBuilder idList = new StringBuilder();
                    ORMDBValueType dbValueType = keyProperty.RuntimeDBValueType;
                    foreach (object id in ids)
                    {
                        if (id != null && DBNull.Value.Equals(id) == false)
                        {
                            if (idList.Length > 0)
                            {
                                idList.Append(",");
                            }
                            if (dbValueType == ORMDBValueType.Text
                                || dbValueType == ORMDBValueType.DateTime)
                            {
                                idList.Append("'" + id + "'");
                            }
                            else
                            {
                                idList.Append(id);
                            }
                        }//if
                    }//foreach
                    if (idList.Length > 0)
                    {
                        ORMTypeMappingHelper childHelper = this.Engine.MappingHelpers[oc];
                        QueryConditionList conditions = new QueryConditionList();
                        conditions.AddCondition(keyProperty.RuntimeDBFieldName, "in", idList.ToString());
                        Array result = this.Engine.ReadInstances(childType, conditions);
                        return result;
                    }
                }
            }
            return null;
        }

        public Array ReadChildIDArray(Type parentType, string[] parentIDs, Type childType)
        {
            CheckState();
            if (parentType == null)
            {
                throw new ArgumentNullException("parentType");
            }
            ORMTypeInfo op = this.Engine.Project.Types[parentType];
            if (op == null)
            {
                throw new NotSupportedException(parentType.FullName);
            }

            if (childType == null)
            {
                throw new ArgumentNullException("childType");
            }
            ORMTypeInfo oc = this.Engine.Project.Types[childType];
            if (oc == null)
            {
                throw new NotSupportedException(childType.FullName);
            }
            return ReadChildIDArray(op.TableName, parentIDs, oc.TableName);
        }


        public Array ReadChildIDArray(
            string parentTableName, 
            string[] parentIDs, 
            string childTableName)
        {
            CheckState();
            if (Util.HasContent(parentTableName) == false)
            {
                throw new ArgumentNullException("parentTableName");
            }
            if (parentIDs == null)
            {
                throw new ArgumentNullException("parentIDs");
            }
            if (Util.HasContent(childTableName) == false)
            {
                throw new ArgumentNullException("childTableName");
            }
            
            parentTableName = parentTableName.Trim().ToUpper();
            //parentID = parentID.Trim();
            childTableName = childTableName.Trim().ToUpper();
            string parentIDList = "";
            if (parentIDs.Length == 0)
            {
                return null;
            }
            else if (parentIDs.Length == 1)
            {
                parentIDList = parentIDs[0];
            }
            else
            {
                parentIDList = Util.Contact(ORMDBValueType.Text, parentIDs);
            }

            int tableNameCompare = string.Compare( childTableName , parentTableName );
            //if( tableNameCompare == 0 )
            //{
            //    throw new ArgumentNullExceptiona("ParentTableName = ChildTableName");
            //}
            using (ORMDBCommand cmd = new ORMDBCommand(this.Engine))
            {
                StringBuilder mySQL = new StringBuilder();
                if (tableNameCompare > 0)
                {
                    mySQL.Append("Select ChildID from Table_EntryRelation Where ParentTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ParentTableName1", parentTableName, this.Engine.ParameterStyle, cmd);
                    if (parentIDs.Length > 1)
                    {
                        mySQL.Append(" And ParentID in (" + parentIDList + ") ");
                    }
                    else
                    {
                        mySQL.Append(" And ParentID = ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ParentID1", parentIDList , this.Engine.ParameterStyle, cmd);
                    }
                    mySQL.Append(" And ChildTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ChildTableName1", childTableName, this.Engine.ParameterStyle, cmd);
                }
                else
                {
                    mySQL.Append("Select ParentID From Table_EntryRelation Where ChildTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ChildTableName2", parentTableName, this.Engine.ParameterStyle, cmd);
                    if (parentIDs.Length > 1)
                    {
                        mySQL.Append(" And ChildID in (" + parentIDList + " ) ");
                    }
                    else
                    {
                        mySQL.Append(" And ChildID = ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ChildID2", parentIDList, this.Engine.ParameterStyle, cmd);
                    }
                    mySQL.Append(" And ParentTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ParentTableName2", childTableName , this.Engine.ParameterStyle, cmd);
                }
                cmd.CommandText = mySQL.ToString();

                this.Engine.BeforeExecuteCommand(null, cmd);

                System.Data.IDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                System.Collections.ArrayList list = new System.Collections.ArrayList();
                while (reader.Read())
                {
                    if (reader.IsDBNull(0) == false)
                    {
                        list.Add(reader.GetValue(0));
                    }
                }//while
                reader.Close();
                return list.ToArray();
            }
        }

        public int SaveRelation(
            string parentTableName,
            string parentID, 
            string childTableName,
            string[] childIDs)
        {
            CheckState();
            if (Util.HasContent(parentTableName) == false)
            {
                throw new ArgumentNullException("parentTableName");
            }
            if ( Util.HasContent( parentID ) == false )
            {
                throw new ArgumentNullException("parentKeyValue");
            }
            if ( Util.HasContent( childTableName ) == false )
            {
                throw new ArgumentNullException("childTableName");
            }
            int result = 0;
            parentTableName = parentTableName.Trim().ToUpper();
            parentID = parentID.Trim();
            childTableName = childTableName.Trim().ToUpper();
            int tableNameCompare = string.Compare(childTableName, parentTableName);
            //if( tableNameCompare == 0 )
            //{
            //    throw new ArgumentNullExceptiona("ParentTableName = ChildTableName");
            //}

            using (ORMDBCommand cmd = new ORMDBCommand(this.Engine))
            {
                StringBuilder mySQL = new StringBuilder();
                // Delete old records
                if (tableNameCompare > 0)
                {
                    mySQL.Append("Delete From Table_EntryRelation Where ParentTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ParentTableName1", parentTableName, this.Engine.ParameterStyle, cmd);
                    mySQL.Append(" And ParentID = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ParentID1", parentID, this.Engine.ParameterStyle, cmd);
                    mySQL.Append(" And ChildTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ChildTableName1", childTableName, this.Engine.ParameterStyle, cmd);
                }
                else
                {
                    mySQL.Append("Delete From Table_EntryRelation Where ChildTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ChildTableName1", parentTableName ,this.Engine.ParameterStyle, cmd);
                    mySQL.Append(" And ChildID = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ChildID1", parentID, this.Engine.ParameterStyle, cmd);
                    mySQL.Append(" And ParentTableName = ");
                    ORMDBCommand.AppendParameterSQL(mySQL, "ParentTableName1", childTableName, this.Engine.ParameterStyle, cmd);
                }
                cmd.CommandText = mySQL.ToString();
                this.Engine.BeforeExecuteCommand(null, cmd);
                result = cmd.ExecuteNonQuery();
                // Insert new relation
                if (childIDs != null && childIDs.Length > 0)
                {
                    // Alloc record id
                    cmd.Parameters.Clear();
                    cmd.CommandText = "Select Max( RecordID ) + 1 From Table_EntryRelation";
                    cmd.Parameters.Clear();
                    this.Engine.BeforeExecuteCommand(null, cmd);
                    int recordID = Convert.ToInt32(cmd.ExecuteScalar());
                    // Insert new records
                    mySQL = new StringBuilder();
                    string firstSQL = "Insert Into Table_EntryRelation ( RecordID , ParentTableName , ParentID , ChildTableName , ChildID ) Values ( ";
                    if (this.Engine.ParameterStyle == ParameterStyle.Macro)
                    {
                        cmd.Parameters.Clear();
                        foreach (string childID in childIDs)
                        {
                            if (tableNameCompare > 0)
                            {
                                cmd.CommandText = firstSQL
                                    + recordID.ToString() + " , '"
                                    + parentTableName + "' , '"
                                    + parentID + "' , '"
                                    + childTableName + "' , '"
                                    + childID + "')";
                            }
                            else
                            {
                                cmd.CommandText = firstSQL
                                    + recordID.ToString() + " , '"
                                    + childTableName + "' , '"
                                    + childID + "' , '"
                                    + parentTableName + "' , '"
                                    + parentID + "')";
                            }
                            this.Engine.BeforeExecuteCommand(null, cmd);
                            result += cmd.ExecuteNonQuery();
                            recordID++;
                        }//foreach
                    }
                    else
                    {
                        mySQL = new StringBuilder();
                        mySQL.Append(firstSQL);
                        ORMDBCommand.AppendParameterSQL(mySQL, "RecordID", DBNull.Value, this.Engine.ParameterStyle, cmd);
                        mySQL.Append(" , ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ParentTableName", parentTableName, this.Engine.ParameterStyle, cmd);
                        mySQL.Append(" , ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ParentID", parentID, this.Engine.ParameterStyle, cmd);
                        mySQL.Append(" , ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ChildTableName", childTableName, this.Engine.ParameterStyle, cmd);
                        mySQL.Append(" , ");
                        ORMDBCommand.AppendParameterSQL(mySQL, "ChildID", DBNull.Value, this.Engine.ParameterStyle, cmd);
                        mySQL.Append(" )");
                        cmd.CommandText = mySQL.ToString();
                        foreach (string childID in childIDs)
                        {
                            if (Util.HasContent(childID))
                            {
                                if (tableNameCompare > 0)
                                {
                                    cmd.Parameters[0].Value = recordID;
                                    cmd.Parameters[1].Value = parentTableName;
                                    cmd.Parameters[2].Value = parentID;
                                    cmd.Parameters[3].Value = childTableName;
                                    cmd.Parameters[4].Value = childID;
                                }
                                else
                                {
                                    cmd.Parameters[0].Value = recordID;
                                    cmd.Parameters[1].Value = childTableName;
                                    cmd.Parameters[2].Value = childID;
                                    cmd.Parameters[3].Value = parentTableName;
                                    cmd.Parameters[4].Value = parentID;
                                }
                                this.Engine.BeforeExecuteCommand(null, cmd);
                                result += cmd.ExecuteNonQuery();
                                recordID++;
                            }//if
                        }//foreach
                    }//else
                }//if
            }//using
            return result;
        }//public int SaveRelation(string parentTableName, string parentID, string childTableName, string[] childIDs)
    }
}
